package com.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.pojo.*;
import com.util.*;

public class CaseDao {
	public int tabCount;
	private String tableName = "t_case";
	private String tableMainKey = "caseId";
	private Connection conn = null;
	private java.sql.ResultSet rs = null;
	java.sql.PreparedStatement pstmt = null;

	// 关闭数据库连接
	public void closeAll() {
		DbUtil.close(pstmt, rs, conn);
	}

	public List<CasePojo> loadCase(int start, int number, String sWhere) {
		List<CasePojo> list = new ArrayList<CasePojo>();
		if (conn == null)
			conn = DbUtil.getConnection();
		try {
			// 按日期排序
			String sql = "select * from " + tableName + " " + sWhere
					+ " order by publishDate desc limit " + number * (start - 1)
					+ "," + number + ";";
			Log.write(sql);
			java.sql.PreparedStatement pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			CasePojo pojo;
			while (rs.next()) {
				pojo = new CasePojo();
				pojo.setCaseId(rs.getInt("caseId"));
				pojo.setCaseName(rs.getString("caseName"));
				pojo.setContent(rs.getString("content"));
				pojo.setPublishDate(rs.getTimestamp("publishDate"));
				pojo.setPublishStatus(rs.getInt("publishStatus"));
				list.add(pojo);
			}
			getCount(sWhere);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return list;
	}

	public CasePojo loadLastPublishedCase() {
		return loadCase(1, 1, " where 1=1 and publishStatus='1' ").get(0);
	}
	
	public String[] loadCaseName() {
		ArrayList<String> allCaseNames = new ArrayList<String>();
		if (conn == null)
			conn = DbUtil.getConnection();
		try {
			// 按日期排序
			String sql = "select caseName from " + tableName + " order by caseId;";
			Log.write(sql);
			java.sql.PreparedStatement pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				allCaseNames.add(rs.getString("caseName"));
			}
			getCount("");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return (String[])allCaseNames.toArray(new String[allCaseNames.size()]);
	}
	
	public boolean updateCasePublishStatus(int caseId, int publishStatus) {
		if (conn == null)
			conn = DbUtil.getConnection();
		boolean flag = false;
		try {
			pstmt = conn.prepareStatement("update " + tableName
					+ " set publishStatus=? where " + tableMainKey + "=?");
			pstmt.setInt(1, publishStatus);
			pstmt.setInt(2, caseId);
			pstmt.execute();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public int deleteCase(String sWhere) {
		int result = -1;
		try {
			if (conn == null)
				conn = DbUtil.getConnection();
			pstmt = conn.prepareStatement("delete from " + tableName + " where " + sWhere);
			pstmt.execute();
			result = 0;
		} catch (Exception ex) {
			Log.write(ex);
		}
		return result;
	}
	
	public int addCase(CasePojo pojo) {
		if (conn == null)
			conn = DbUtil.getConnection();
		int flag = -1;
		try {

			String sql = "insert into "
					+ tableName
					+ "(caseName, content, publishDate, publishStatus) "
					+ "values(?,?,?,?);";
			Log.write(sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pojo.getCaseName());
			pstmt.setString(2, pojo.getContent());
			pstmt.setTimestamp(3,pojo.getPublishDate());
			pstmt.setInt(4, pojo.getPublishStatus());
			pstmt.execute();
			flag = 1;
		} catch (Exception e) {
			Log.write(e);
		}
		return flag;
	}

	private void getCount(String sWhere) {
		tabCount = 0;
		try {
			if (conn == null)
				conn = DbUtil.getConnection();
			pstmt = conn.prepareStatement("select count(*) as count from "
					+ tableName + " " + sWhere);
			rs = pstmt.executeQuery();
			if (rs.next())
				tabCount = rs.getInt("count");
		} catch (Exception ex) {
			ex.fillInStackTrace();
		}
	}
}
